/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package marketplace_server;

/**
 *
 * @author zno
 */
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import marketplace_bankserver.Account;
import marketplace_bankserver.AccountImpl;
import marketplace_client.Client;
import marketplace_client.ClientImpl;

public class SQLiteManager {

    private Connection connection;
    private ResultSet resultSet;
    private Statement statement;
    private PreparedStatement prep;

    public SQLiteManager() {

        establishConnection();
    }
    public void close() throws SQLException{
        resultSet.close();
        connection.close();
    }
    public void establishConnection() {

        try {
            Class.forName("org.sqlite.JDBC");
            connection = DriverManager.getConnection("jdbc:sqlite:homework3.sqlite");
            statement = connection.createStatement();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    // CRUD Functionality for the Item Entity

    public ArrayList<Item> getItems() throws SQLException {
        ArrayList<Item> items = new ArrayList<Item>();

        resultSet = statement.executeQuery("SELECT * FROM ITEMS;");

        for (int i = 1; resultSet.next(); i++) {

            String name = resultSet.getString("name");
            int price = resultSet.getInt("price");
            String sellerUsername = resultSet.getString("seller");
            boolean isForSale = resultSet.getBoolean("isforsale");
            //User seller = getUser(sellerUsername);
            String buyerUsername = resultSet.getString("buyer");

            Item item = new Item(name, price, isForSale, sellerUsername, buyerUsername);
            items.add(item);
        }

        return items;

    }
    
    public Item getItem(String name) throws SQLException {
        HashMap<String, Item> items = new HashMap<String, Item>();

        resultSet = statement.executeQuery("SELECT * FROM ITEMS where name='"+name+"';");

        if (resultSet.next()){
            

            
            int price = resultSet.getInt("price");
            String sellerUsername = resultSet.getString("seller");
            boolean isForSale = resultSet.getBoolean("isforsale");
            
            String buyerUsername = resultSet.getString("buyer");

            return new Item(name, price, isForSale, sellerUsername, buyerUsername);
            
        }
        return null;
        

    }
    
    

    public void registerItem(Item item) throws SQLException {
        String bool;
        if (item.isForSale()) {
            bool = "true";
        } else {
            bool = "false";
        }

        statement.executeUpdate("INSERT INTO items VALUES('" + item.getName() + "'," + item.getPrice() + ",'" + item.getSeller() + "','','" + bool + "')");


    }

    public void updateItem(Item item) throws SQLException {
        String bool;
        if (item.isForSale()) {
            bool = "true";
        } else {
            bool = "false";
        }
        statement.executeUpdate("update items SET price="
                + item.getPrice() + ",seller='" + item.getSeller() + "',isforsale='" + bool
                + "' where name='" + item.getName() + "';");


    }

    public void deleteItem(Item item) throws SQLException {
        statement.executeUpdate("delete from items where name='" + item.getName() + "';");
    }

    // CRUD Functionality for the Client Entity
    public void registerUser(String username, String password) throws SQLException {
        statement.executeUpdate("INSERT INTO users VALUES ('" + username + "','" + password + "',0,0);");


    }

    // CRUD Functionality for the Account Entity
    public HashMap<String, Account> getAccounts() {
        HashMap<String, Account> accounts = new HashMap<String, Account>();

        return accounts;
    }

    public void createAccount(AccountImpl account) {
    }

    public void updateAccount(AccountImpl account) {
    }

    public void deleteAccount(AccountImpl account) {
    }

    public User getUser(String sellerUsername) throws SQLException {
        resultSet = statement.executeQuery("SELECT * FROM USERS where username='" + sellerUsername + "' ;");

        String password=null;
        int nItemsSold=0;
        int nItemsBought=0;

        if (resultSet.next()) {

            password = resultSet.getString("password");
            nItemsSold = resultSet.getInt("nitemssold");
            nItemsBought = resultSet.getInt("nitemsbought");
            return new User(sellerUsername, password, nItemsBought, nItemsSold);
        }else
            return null;

        
        

    }
}
